package cn.edu.zhku.citrusmonitor.person.user.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.catalina.tribes.util.Arrays;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.zhku.citrusmonitor.domain.TbUser;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;

public class UserDaoImpl implements UserDao{
	
	
	
	
	BeanProcessor bean=new GenerousBeanProcessor();
	RowProcessor processor=new BasicRowProcessor(bean);
	/**
	 * 添加用户
	 */
	@Override
	public void add(TbUser user) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		List<String> list=new ArrayList<String>();
		
		String sql="insert into user(user_id,phone,password,real_name,gender,email";
		
		list.add(user.getUserId());
		list.add(user.getPhone());
		list.add(user.getPassword());
		list.add(user.getRealName());
		list.add(user.getGender());
		list.add(user.getEmail());
		if(user.getRole()!=null&&!user.getRole().equals("")) {
			sql+=",role";
			list.add(user.getRole());
			sql+=") values(?,?,?,?,?,?,?)";
		}else
			sql+=") values(?,?,?,?,?,?)";
		qr.update(sql,list.toArray());
	}
	/**
	 * 获取用户通过 手机号
	 * @throws SQLException 
	 */
	@Override
	public TbUser getUserByPhone(String phone) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from user where phone=?";
		return qr.query(sql,new BeanHandler<TbUser>(TbUser.class,processor),phone);
		
	}
	/**
	 * 获取用户通过手机号和密码
	 * @throws SQLException 
	 */
	@Override
	public TbUser getUserByPhoneAndPwd(String phone, String password) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from user where phone=? and password=?";
		//System.out.println(qr.query(sql,new BeanHandler<TbUser>(TbUser.class,processor),phone,password));
		return qr.query(sql, new BeanHandler<TbUser>(TbUser.class,processor),phone,password);
		
	}
	/**
	 * 获得总记录数
	 * @throws SQLException 
	 */
	@Override
	public long getTotal() throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select count(*) from user";
		
		return (long) qr.query(sql, new ScalarHandler(1));
	}
	
	/**
	 * 获取分页对象全部信息的集合
	 */
	@Override
	public List<TbUser> getList(int currentPage, long pageSize) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from user order by register_time desc limit ?,? ";
		return qr.query(sql, new BeanListHandler<TbUser>(TbUser.class,processor),(currentPage-1)*pageSize,pageSize);
		//return null;
	}
	
	
	
	/**
	 * 	根据对象查询该id
	 */
	/*@Override
	public String getUserIdByUser(TbUser user) {
		String sql="select user_id from user where 1=1";
		
	}*/
	
	/**
	 * 修改用户信息
	 * @throws SQLException 
	 */
	@Override
	public int modify(TbUser user) throws SQLException {
		
		// TODO Auto-generated method stub
		List<String> list=new ArrayList<>();
		String sql="update user set user_id=?";
		list.add(user.getUserId());
		if(user.getPhone()!=null&&user.getPhone().trim()!="") {
			sql+=",phone=?";
			list.add(user.getPhone());
		}
		if(user.getPassword()!=null&&user.getPassword().trim()!="") {
			sql+=",password=?";
			list.add(user.getPassword());
		}
		if(user.getRealName()!=null&&user.getRealName().trim()!="") {
			sql+=",real_name=?";
			list.add(user.getRealName());
		}
		if(user.getGender()!=null&&user.getGender().trim()!="") {
			sql+=",gender=?";
			list.add(user.getGender());
		}
		if(user.getEmail()!=null&&user.getEmail().trim()!="") {
			sql+=",email=?";
			list.add(user.getEmail());
		}
		if(user.getRole()!=null&&user.getRole().trim()!="") {
			sql+=",role=?";
			list.add(user.getRole());
		}
		sql+=" where user_id=?";
		list.add(user.getUserId());
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		
		return qr.update(sql,list.toArray());
	}
	/**
	 * 根据id删除
	 */
	@Override
	public int deleteUserById(String id) throws SQLException {
		// TODO Auto-generated method stub
		String sql="delete from user where user_id=?";
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		return qr.update(sql,id);
		
		
	}
	/**
	 * 通过id获取用户
	 */
	@Override
	public TbUser getUserById(String id) throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from user where user_id=?";
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		return qr.query(sql, new BeanHandler<TbUser>(TbUser.class,processor),id);
		
	}
	/**
	 * 通过手机号模糊查询
	 * @throws SQLException 
	 */
	@Override
	public List<TbUser> getUserByLikePhone(String phone) throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from user where phone like ?";
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		return qr.query(sql, new BeanListHandler<TbUser>(TbUser.class,processor),"%"+phone+"%");
		 
	}
	@Override
	public int[] removeAllById(String[] ids) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String[][] values=new String[ids.length][];
		for(int i=0;i<ids.length;i++) {
			values[i]=new String[] {ids[i]};
		}
		
		String sql="delete from user where user_id=?";
		
		return qr.batch(sql, values);
		
	}
	
}
